﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using InvestmentIntelligence.Logging.Core;


namespace Invest.DataBaseUpdater.Core
{
    public class UpdateDbProcessor : BaseProcessor
    {
        private readonly LogProcessor logProcessor;
        public UpdateDbProcessor(SqlConnection connection) : base(connection)
        {
            logProcessor=new LogProcessor(connection);
        }

        public void Process()
        {
            OpenConnection();
            var executedFiles = new List<string>();
            try
            {
                executedFiles.AddRange(GetListExecutedFiles());
            }
            catch{}

            var scriptFiles = Directory.GetFiles("DataBaseUpdateScripts", "*.sql", SearchOption.AllDirectories).Except(executedFiles).ToList();

            foreach (var scriptFile in scriptFiles)
            {
                Log.Info("Process script "+scriptFile);
                string sqlQuery;
                string sqlHeader;
                bool skipTransaction;
                ReadSqlFromFile(scriptFile, out sqlQuery, out sqlHeader, out skipTransaction);

                if (string.IsNullOrWhiteSpace(sqlQuery))
                {
                    continue;
                }
                if (skipTransaction)
                {
                    ExecuteWithoutTransaction(sqlQuery, scriptFile, sqlHeader);
                }
                else
                {
                    ExecuteWithTransaction(sqlQuery, scriptFile, sqlHeader);
                }
            }
        }

        private void ExecuteWithTransaction(string sqlQuery, string scriptFile, string sqlHeader)
        {
            var command = Connection.CreateCommand();
            var transaction = Connection.BeginTransaction("UpdateTransaction");
            command.Connection = Connection;
            command.Transaction = transaction;
            command.CommandTimeout = 0;
            try
            {
                var sqlComands = sqlQuery.Split(new[] { "GO\r\n", "GO ", "GO\t" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var cmdText in sqlComands)
                {
                    try
                    {
                        command.CommandText = cmdText;
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        if (ex.Number == 9982//Cannot use full-text search in user instance.
                            || ex.Number == 7609 //Full-Text Search is not installed, or a full-text component cannot be loaded.
                            )
                        {
                            Log.Error(ex.Message + Environment.NewLine + cmdText);
                            continue;
                        }
                        throw;
                    }
                }

                transaction.Commit();
                logProcessor.UpdateRefactorLog(scriptFile, sqlHeader);
            }
            catch (SqlException ex)
            {
                transaction.Rollback();
                throw new ApplicationException(
                    string.Format("Failed to process script: '{0}'.\r\n Error message: '{1}'", scriptFile, ex.Message), ex);
            }
        }

        private void ExecuteWithoutTransaction(string sqlQuery, string scriptFile, string sqlHeader)
        {
            var command = Connection.CreateCommand();
            command.Connection = Connection;
            command.CommandTimeout = 0;
            try
            {
                var sqlComands = sqlQuery.Split(new[] { "GO\r\n", "GO ", "GO\t" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (var cmdText in sqlComands)
                {
                    try
                    {
                        command.CommandText = cmdText;
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        if (ex.Number == 9982//Cannot use full-text search in user instance.
                            || ex.Number == 7609 //Full-Text Search is not installed, or a full-text component cannot be loaded.
                            )
                        {
                            Log.Error(ex.Message + Environment.NewLine + cmdText);
                            continue;
                        }
                        throw;
                    }
                }
                logProcessor.UpdateRefactorLog(scriptFile, sqlHeader);
            }
            catch (SqlException ex)
            {
                throw new ApplicationException(
                    string.Format("Failed to process script: '{0}'.\r\n Error message: '{1}'", scriptFile, ex.Message), ex);
            }
        }
    }
}
